﻿using RQX.Common.Framework.Extension;
using RQX.Common.Framework.Logger;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace RQX.Common.Framework.Db.Base
{
    /// <summary>
    /// 基础数据库帮助类
    /// </summary>
    public abstract class BaseDbHelper<TDbConnection, TCommand>
        where TDbConnection : DbConnection, new()
        where TCommand : DbCommand, new()
    {
        #region 连接配置
        private static string _connectionStr = "";
        private TDbConnection _conn;
        
        public BaseDbHelper()
        {
            if (_connectionStr.IsNullOrEmpty())
            {
                _connectionStr = GetConnectionStr();
            }
        }
        public BaseDbHelper(string connStr)
        {
            if (_connectionStr.IsNullOrEmpty())
            {
                _connectionStr = connStr;
            }
        }
        /// <summary>
        /// 获取连接字符串
        /// </summary>
        /// <returns></returns>
        protected virtual string GetConnectionStr()
        {
            return ConfigurationManager.AppSettings["ConnectionString"].ToString();
        }
        /// <summary>
        /// 获取数据库连接
        /// </summary>
        /// <returns></returns>
        protected virtual TDbConnection GetDbConnection()
        {
            if (_conn.IsNull())
            {
                if (_connectionStr.IsNullOrEmpty())
                {
                    throw new Exception("数据库连接配置异常！");
                }
                _conn = CreateDbConnection(_connectionStr);
                _conn.Open();
            }
            if (_conn.State != ConnectionState.Open && _conn.State != ConnectionState.Connecting) _conn.Open();
            return _conn;
        }
        /// <summary>
        /// 创建数据库连接
        /// </summary>
        /// <param name="connectionStr"></param>
        /// <returns></returns>
        protected abstract TDbConnection CreateDbConnection(string connectionStr);
        /// <summary>
        /// 获取cmd
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        protected TCommand GetCommand(string sql) => new TCommand { Connection = GetDbConnection(), CommandText = sql };
        /// <summary>
        /// 获取cmd带参数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        protected TCommand GetCommand(string sql,DbParameter[] parameters)
        {
            var cmd = new TCommand
            {
                Connection = GetDbConnection(),
                CommandText = sql
            };
            parameters.ForEach(item =>
            {
                cmd.Parameters.Add(item);
            });
            return cmd;
        }

        #endregion

        #region Sql语句执行
        /// <summary>
        /// 执行sql，不进行查询
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>返回受影响的行数</returns>
        public virtual int ExcuteSqlNoQuery(string sql)
        {
            try
            {
                using (var cmd = GetCommand(sql))
                {
                    return cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 查询单个返回值
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public virtual string ExcuteSingleResult(string sql)
        {
            try
            {
                using (var cmd = GetCommand(sql))
                {
                    using (var reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            return reader[0].ToString();
                        }
                        else
                        {
                            return null;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 批量执行sql列表,返回成功的条数
        /// </summary>
        /// <param name="sqlList"></param>
        /// <param name="isUseTransaction">是否启用事务</param>
        /// <returns></returns>
        public virtual int ExcuteSqlNoQuery(IEnumerable<string> sqlList, bool isUseTransaction = false)
        {
            try
            {
                using (var cmd = GetCommand(""))
                {
                    int successNum = 0;
                    if (isUseTransaction)//启用事务
                    {
                        var transaction = GetDbConnection().BeginTransaction();
                        cmd.Transaction = transaction;
                        foreach (var sql in sqlList)
                        {
                            try
                            {
                                cmd.CommandText = sql;
                                cmd.ExecuteNonQuery();
                                successNum++;
                            }
                            catch (Exception ex)
                            {
                                transaction.Rollback();
                                throw ex;
                            }
                        }
                        transaction.Commit();
                        return successNum;
                    }
                    else//不启用事务
                    {
                        sqlList.ForEach(sql =>
                        {
                            try
                            {
                                cmd.CommandText = sql;
                                cmd.ExecuteNonQuery();
                                successNum++;
                            }
                            catch { }
                        });
                        return successNum;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 执行sql查询单个obj，带func的委托
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="func"></param>
        /// <returns></returns>
        public virtual T ExcuteSingleObj<T>(string sql,Func<IDataReader,T> func)
        {
            try
            {
                using (var cmd = GetCommand(sql))
                {
                    using (var reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            return func(reader);
                        }
                        return default;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 执行sql查询多个list，带func的委托
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="func"></param>
        /// <returns></returns>
        public virtual IEnumerable<T> ExcuteList<T>(string sql, Func<IDataReader, T> func)
        {
            try
            {
                using (var cmd = GetCommand(sql))
                {
                    using (var reader = cmd.ExecuteReader())
                    {
                        List<T> resultList = new List<T>();
                        while (reader.Read())
                        {
                            var item = func(reader);
                            if (item.IsNotNull()) resultList.Add(item);
                        }
                        return resultList;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion
    }
}
